//package com.tcoiss.datafactory.util;
//
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
//import java.sql.SQLException;
//import java.sql.Statement;
//
//import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
//import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;
//
//public class BulkCopyMultiple {
//    public static void main(String[] args) {
//        String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";
//        String destinationHeaderTable = "dbo.BulkCopyDemoOrderHeader";
//        String destinationDetailTable = "dbo.BulkCopyDemoOrderDetail";
//        int countHeaderBefore, countDetailBefore, countHeaderAfter, countDetailAfter;
//        ResultSet rsHeader, rsDetail;
//
//        try (Connection sourceConnection1 = DriverManager.getConnection(connectionUrl);
//             Connection sourceConnection2 = DriverManager.getConnection(connectionUrl);
//             Statement stmt = sourceConnection1.createStatement();
//             PreparedStatement preparedStmt1 = sourceConnection1.prepareStatement(
//                     "SELECT [SalesOrderID], [OrderDate], [AccountNumber] FROM [Sales].[SalesOrderHeader] WHERE [AccountNumber] = ?;");
//             PreparedStatement preparedStmt2 = sourceConnection2.prepareStatement(
//                     "SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], [OrderQty], [ProductID], [UnitPrice] FROM "
//                             + "[Sales].[SalesOrderDetail] INNER JOIN [Sales].[SalesOrderHeader] ON "
//                             + "[Sales].[SalesOrderDetail].[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] WHERE [AccountNumber] = ?;");
//             SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connectionUrl);) {
//
//            // Empty the destination tables.
//            stmt.executeUpdate("DELETE FROM " + destinationHeaderTable);
//            stmt.executeUpdate("DELETE FROM " + destinationDetailTable);
//
//            // Perform an initial count on the destination
//            // table with matching columns.
//            countHeaderBefore = getRowCount(stmt, destinationHeaderTable);
//
//            // Perform an initial count on the destination
//            // table with different column positions.
//            countDetailBefore = getRowCount(stmt, destinationDetailTable);
//
//            // Get data from the source table as a ResultSet.
//            // The Sales.SalesOrderHeader and Sales.SalesOrderDetail
//            // tables are quite large and could easily cause a timeout
//            // if all data from the tables is added to the destination.
//            // To keep the example simple and quick, a parameter is
//            // used to select only orders for a particular account
//            // as the source for the bulk insert.
//            preparedStmt1.setString(1, "10-4020-000034");
//            rsHeader = preparedStmt1.executeQuery();
//
//            // Get the Detail data in a separate connection.
//            preparedStmt2.setString(1, "10-4020-000034");
//            rsDetail = preparedStmt2.executeQuery();
//
//            // Create the SQLServerBulkCopySQLServerBulkCopy object.
//            SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
//            copyOptions.setBulkCopyTimeout(100);
//            bulkCopy.setBulkCopyOptions(copyOptions);
//            bulkCopy.setDestinationTableName(destinationHeaderTable);
//
//            // Guarantee that columns are mapped correctly by
//            // defining the column mappings for the order.
//            bulkCopy.addColumnMapping("SalesOrderID", "SalesOrderID");
//            bulkCopy.addColumnMapping("OrderDate", "OrderDate");
//            bulkCopy.addColumnMapping("AccountNumber", "AccountNumber");
//
//            // Write rsHeader to the destination.
//            bulkCopy.writeToServer(rsHeader);
//
//            // Set up the order details destination.
//            bulkCopy.setDestinationTableName(destinationDetailTable);
//
//            // Clear the existing column mappings
//            bulkCopy.clearColumnMappings();
//
//            // Add order detail column mappings.
//            bulkCopy.addColumnMapping("SalesOrderID", "SalesOrderID");
//            bulkCopy.addColumnMapping("SalesOrderDetailID", "SalesOrderDetailID");
//            bulkCopy.addColumnMapping("OrderQty", "OrderQty");
//            bulkCopy.addColumnMapping("ProductID", "ProductID");
//            bulkCopy.addColumnMapping("UnitPrice", "UnitPrice");
//
//            // Write rsDetail to the destination.
//            bulkCopy.writeToServer(rsDetail);
//
//            // Perform a final count on the destination
//            // tables to see how many rows were added.
//            countHeaderAfter = getRowCount(stmt, destinationHeaderTable);
//            countDetailAfter = getRowCount(stmt, destinationDetailTable);
//
//            System.out.println((countHeaderAfter - countHeaderBefore) + " rows were added to the Header table.");
//            System.out.println((countDetailAfter - countDetailBefore) + " rows were added to the Detail table.");
//        }
//        // Handle any errors that may have occurred.
//        catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
//
//    private static int getRowCount(Statement stmt,
//                                   String tableName) throws SQLException {
//        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
//        rs.next();
//        int count = rs.getInt(1);
//        rs.close();
//        return count;
//    }
//}
